﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using DAO_DTO_WS.DTO;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace DAO_DTO_WS.DAO
{
    /// <summary>
    /// -1: loi
    /// 0: dang xet duyet
    /// 1: sap ban
    /// 2: dang ban
    /// 3: het ban
    /// </summary>
    public class SanPhamDAO : Abstract
    {
        public static bool ThemMoi(SanPhamDTO sanPham)
        {
            SqlConnection connection = KetNoi();
            bool KetQua = false;
            String SqlString = "INSERT INTO SANPHAM(TieuDe,DiemNoiBat,DieuKien,ThongTin,ThoiGianBatDau,ThoiGianKetThuc,GiaGoc,GiaTietKiem,SoLuong,SoLuongToiThieu,SoLuongToiDa,MaDiaPhuong,MaDoanhNghiep,SoLanXem,TinhTrang) VALUES " +
                 "(@TieuDe,@DiemNoiBat,@DieuKien,@ThongTin,@ThoiGianBatDau,@ThoiGianKetThuc,@GiaGoc,@GiaTietKiem,@SoLuong,@SoLuongToiThieu,@SoLuongToiDa,@MaDiaPhuong,@MaDoanhNghiep,@SoLanXem,@TinhTrang)";
            try
            {
                SqlCommand command = new SqlCommand(SqlString, connection);
                addThuocTinhSanPham(sanPham, command);
                
                int rows = 0;
                rows = command.ExecuteNonQuery();
                if (rows == 1)
                    KetQua = true;
            }
            catch (Exception ex)
            {
                KetQua = false;
                throw ex;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return KetQua;
        }
        public static bool CapNhatDanhSachSanPham()
        {
            bool ketqua = false;
            List<SanPhamDTO> dssp = layDSSanPham();
            for (int index = 0; index < dssp.Count(); ++index)
            {
                int tt = dssp[index].TinhTrang;
                if ((tt == 1) || (tt == 2))
                {
                    if ((dssp[index].ThoiGianBatDau <= DateTime.Now) && (DateTime.Now <= dssp[index].ThoiGianKetThuc))
                        tt = 2;
                    else if (dssp[index].ThoiGianKetThuc < DateTime.Now)
                        tt = 3;
                    else
                    {
                        tt = 1;
                        dssp[index].SoLuong = 0;
                        dssp[index].SoLanXem = 0;
                    }
                    dssp[index].TinhTrang = tt;
                }
                CapNhatSanPham(dssp[index]);
            }
            ketqua = true;
            return ketqua;
        }
        public static bool CapNhatSanPham(SanPhamDTO sanPham)
        {
            SqlConnection connection = KetNoi();
            bool KetQua = false;
            String SqlString = "UPDATE SANPHAM " +
                "SET TieuDe=@TieuDe,DiemNoiBat=@DiemNoiBat,DieuKien=@DieuKien,ThongTin=@ThongTin,ThoiGianBatDau=@ThoiGianBatDau,ThoiGianKetThuc=@ThoiGianKetThuc,GiaGoc=@GiaGoc,GiaTietKiem=@GiaTietKiem,SoLuong=@SoLuong,SoLuongToiThieu=@SoLuongToiThieu,SoLuongToiDa=@SoLuongToiDa,MaDiaPhuong=@MaDiaPhuong,MaDoanhNghiep=@MaDoanhNghiep,SoLanXem=@SoLanXem,TinhTrang=@TinhTrang" +
                " WHERE MaSanPham=@MaSanPham";
            try
            {
                SqlCommand command = new SqlCommand(SqlString, connection);
                addThuocTinhSanPham(sanPham, command);
                command.Parameters.AddWithValue("@MaSanPham", sanPham.MaSanPham);
                int rows = 0;
                rows = command.ExecuteNonQuery();
                if (rows == 1)
                    KetQua = true;
            }
            catch (Exception ex)
            {
                KetQua = false;
                throw ex;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return KetQua;
        }

        //Ham viet de test
        public static List<SanPhamDTO> layDSSanPham()
        {
            List<SanPhamDTO> dsSanPham = new List<SanPhamDTO>();
            SqlConnection connection = KetNoi();
            string sqlquery = "SELECT MaSanPham,TieuDe,DiemNoiBat,DieuKien,ThongTin,ThoiGianBatDau,ThoiGianKetThuc,"
            +"GiaGoc,GiaTietKiem,SoLuong,SoLuongToiThieu,SoLuongToiDa,MaDiaPhuong,MaDoanhNghiep,SoLanXem,TinhTrang FROM SANPHAM";
            try
            {
                SqlCommand command = new SqlCommand(sqlquery, connection);
                command.CommandText = sqlquery;
                command.CommandType = CommandType.Text;
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    SanPhamDTO sp = new SanPhamDTO();
                    sp.MaSanPham = Convert.ToInt32(reader["MaSanPham"]);
                    sp.TieuDe = reader["TieuDe"].ToString();
                    sp.DiemNoiBat = reader["DiemNoiBat"].ToString();
                    sp.DieuKien = reader["DieuKien"].ToString();
                    sp.ThongTin = reader["ThongTin"].ToString();
                    sp.ThoiGianBatDau = Convert.ToDateTime(reader["ThoiGianBatDau"]);
                    sp.ThoiGianKetThuc = Convert.ToDateTime(reader["ThoiGianKetThuc"]);
                    sp.GiaGoc = Convert.ToInt64(reader["GiaGoc"]);
                    sp.GiaTietKiem =Convert.ToInt64(reader["GiaTietKiem"]);
                    sp.SoLuong = Convert.ToInt32(reader["SoLuong"]);
                    sp.SoLuongToiThieu = Convert.ToInt32(reader["SoLuongToiThieu"]);
                    sp.MaDiaPhuong = Convert.ToInt32(reader["MaDiaPhuong"]);
                    sp.SoLuongToiDa = Convert.ToInt32(reader["SoLuongToiDa"]);
                    sp.MaDoanhNghiep = Convert.ToInt32(reader["MaDoanhNghiep"]);
                    sp.SoLanXem = Convert.ToInt32(reader["SoLanXem"]);
                    sp.TinhTrang = Convert.ToInt32(reader["TinhTrang"]);

                    dsSanPham.Add(sp);
                }
            }
            catch (Exception ex)
            {
                dsSanPham = new List<SanPhamDTO>();
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return dsSanPham;
        }
        private static void addThuocTinhSanPham(SanPhamDTO sanPham, SqlCommand command)
        {
            command.Parameters.AddWithValue("@TieuDe", sanPham.TieuDe);
            command.Parameters.AddWithValue("@DiemNoiBat", sanPham.DiemNoiBat);
            command.Parameters.AddWithValue("@DieuKien", sanPham.DieuKien);
            command.Parameters.AddWithValue("@ThongTin", sanPham.ThongTin);
            command.Parameters.AddWithValue("@ThoiGianBatDau", sanPham.ThoiGianBatDau);
            command.Parameters.AddWithValue("@ThoiGianKetThuc", sanPham.ThoiGianKetThuc);
            command.Parameters.AddWithValue("@GiaGoc", sanPham.GiaGoc);
            command.Parameters.AddWithValue("@GiaTietKiem", sanPham.GiaTietKiem);
            command.Parameters.AddWithValue("@SoLuong", sanPham.SoLuong);
            command.Parameters.AddWithValue("@SoLuongToiThieu", sanPham.SoLuongToiThieu);
            command.Parameters.AddWithValue("@SoLuongToiDa", sanPham.SoLuongToiDa);
            command.Parameters.AddWithValue("@MaDiaPhuong", sanPham.MaDiaPhuong);
            command.Parameters.AddWithValue("@MaDoanhNghiep", sanPham.MaDoanhNghiep);
            command.Parameters.AddWithValue("SoLanXem", sanPham.SoLanXem);
            command.Parameters.AddWithValue("@TinhTrang", sanPham.TinhTrang);
        }
        public static SanPhamDTO laySPTheoMa(int maSp)
        {
            SanPhamDTO SanPham = new SanPhamDTO();
            SqlConnection connection = KetNoi();
            string sqlstring = "SELECT * from SANPHAM where MaSanPham =@MaSP";
            try
            {
                SqlCommand command = new SqlCommand(sqlstring, connection);
                command.Parameters.AddWithValue("@MaSp",maSp);
                SqlDataReader reader = command.ExecuteReader();
                reader.Read();
                DocDuLieu(reader, SanPham);
            }
            catch
            {
                SanPham = new SanPhamDTO();
                SanPham.TinhTrang =-1;
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return SanPham;
        }
        public static List<SanPhamDTO> laySPTheoTinhTrang(int tinhtrang)
        {
            List<SanPhamDTO> dsSanPham = new List<SanPhamDTO>();
            SqlConnection connection = KetNoi();
            string sqlstring = "SELECT * from SANPHAM where TinhTrang =@tinhtrang";
            try
            {
                SqlCommand command = new SqlCommand(sqlstring, connection);
                command.Parameters.AddWithValue("@tinhtrang", tinhtrang);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    SanPhamDTO sp= new SanPhamDTO();
                    DocDuLieu(reader, sp);
                    dsSanPham.Add(sp);
                }
            }
            catch
            {
                dsSanPham = new List<SanPhamDTO>();
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return dsSanPham;
        }
        private static void DocDuLieu(SqlDataReader reader, SanPhamDTO SanPham)
        {
            SanPham.MaSanPham = (int)reader[0];
            SanPham.TieuDe = reader[1].ToString();
            SanPham.DiemNoiBat = reader[2].ToString();
            SanPham.DieuKien = reader[3].ToString();
            SanPham.ThongTin = reader[4].ToString();
            SanPham.ThoiGianBatDau = Convert.ToDateTime(reader[5]);
            SanPham.ThoiGianKetThuc = Convert.ToDateTime(reader[6]);
            SanPham.GiaGoc = (long)reader[7];
            SanPham.GiaTietKiem = (long)reader[8];
            SanPham.SoLuong = (int)reader[9];
            SanPham.SoLuongToiThieu = (int)reader[10];
            SanPham.SoLuongToiDa = (int)reader[11];
            SanPham.MaDiaPhuong = (int)reader[12];
            SanPham.MaDoanhNghiep = (int)reader[13];
            SanPham.SoLanXem =(int)(reader[14]);
            SanPham.TinhTrang = (int)reader[15];
        }
        public static List<SanPhamDTO> laySPTheoNgay(DateTime ngay)
        {
            List<SanPhamDTO> dsSanPham = new List<SanPhamDTO>();
            SqlConnection connection = KetNoi();
            string sqlstring = "SELECT * from SANPHAM where ThoiGianBatDau <= @ngay and @ngay<=ThoiGianKetThuc";
            try
            {
                SqlCommand command = new SqlCommand(sqlstring, connection);
                command.Parameters.AddWithValue("@ngay", ngay);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    SanPhamDTO sp = new SanPhamDTO();
                    DocDuLieu(reader, sp);
                    dsSanPham.Add(sp);
                }
            }
            catch
            {
                dsSanPham = new List<SanPhamDTO>();
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return dsSanPham;
        }
        public static List<SanPhamDTO> laySPTheoDiaPhuong(int maDiaPhuong)
        {
            List<SanPhamDTO> dsSanPham = new List<SanPhamDTO>();
            SqlConnection connection = KetNoi();
            string sqlstring = "SELECT * from SANPHAM where MaDiaPhuong =@DiaPhuong";
            try
            {
                SqlCommand command = new SqlCommand(sqlstring, connection);
                command.Parameters.AddWithValue("@DiaPhuong", maDiaPhuong);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    SanPhamDTO sp = new SanPhamDTO();
                    DocDuLieu(reader, sp);
                    dsSanPham.Add(sp);
                }
            }
            catch
            {
                dsSanPham = new List<SanPhamDTO>();
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return dsSanPham;
        }

        public static List<DateTime> layNgayHetHanTheoMaSanpham(int maSanPham)
        {
            List<DateTime> dsSanPham = new List<DateTime>();
            SqlConnection connection = KetNoi();
            string sqlstring = "SELECT ThoiGianKetThuc from SANPHAM where MaSanPham =@MaSanPham";
            try
            {
                SqlCommand command = new SqlCommand(sqlstring, connection);
                command.Parameters.AddWithValue("@MaSanPham", maSanPham);
                SqlDataReader reader = command.ExecuteReader();
                while (reader.Read())
                {
                    DateTime sp = new DateTime();
                    sp = (DateTime)reader[0];
                    dsSanPham.Add(sp);
                }
            }
            catch
            {
                dsSanPham = new List<DateTime>();
            }
            finally
            {
                if ((connection != null) && (connection.State == ConnectionState.Open))
                    connection.Close();
            }
            return dsSanPham;
        }

    }
}
